/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Utilities;
import java.util.Date;
import java.sql.ResultSet;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 *
 * @author Carlos
 */
public class DataAccesor
{
    private Connection _conn;

    // <editor-fold defaultstate="collapsed" desc="Singleton">
    private static DataAccesor _instance = new DataAccesor();

    private DataAccesor()
    {
        try
        {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            _conn = java.sql.DriverManager.getConnection(Constants.SQL_CONNECTION_STRING);
        }
        catch(Exception e)
        {
            //e.printStackTrace();
        }
    }

    public static DataAccesor GetInstance()
    {
        return _instance;
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="Public Methods">
    public static Connection GetNewConnection() throws ClassNotFoundException, SQLException
    {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        return DriverManager.getConnection(Constants.SQL_CONNECTION_STRING);
    }

    public static int GetStartRowForSP(int pageNumber, int displayItems)
    {
        return (pageNumber - 1) * displayItems + 1;
    }

    public static int GetEndRowForSP(int pageNumber, int displayItems)
    {
        return pageNumber * displayItems;
    }


    public ResultSet ExecuteInputSP(String spName, SQLParameter[] parameters) throws SQLException
    {
        Connection conn = getConnection();
        ResultSet result = executeSP(conn, spName, parameters);
        return result;
    }

    public boolean ExecuteOutputSP(String spName, SQLParameter[] parameters) throws SQLException
    {
        boolean boolResult = true;
        Connection conn = getConnection();
        ResultSet result = executeSP(conn, spName, parameters);
        if (result == null)
            boolResult = false;
        return boolResult;
    }

    public void CloseConnection(ResultSet result) throws SQLException
    {
        try
        {
            Connection conn = result.getStatement().getConnection();
            result.getStatement().close();
            if(conn != null && !conn.isClosed())
                conn.close();
            conn = null;
        }
        catch(Exception e)
        {
            //e.printStackTrace();
        }
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="Protected/Private Methods">
    private ResultSet executeSP(Connection conn, String spName, SQLParameter[] parameters) throws SQLException
    {
        spName = "{ call " + spName + " ( ";
        if(parameters.length > 0)
        {
            spName += "? ";
            for(int paramIndex = 1; paramIndex < parameters.length; paramIndex++)
                spName += ",? ";
        }
        spName += ") }";
        CallableStatement statement = conn.prepareCall(spName);
        for(int paramIndex = 0; paramIndex < parameters.length; paramIndex++)
            if(parameters[paramIndex].getValue() instanceof Date)
            {
                Date date = (Date) parameters[paramIndex].getValue();
                statement.setDate(parameters[paramIndex].getName(), new java.sql.Date(date.getYear(), date.getMonth(), date.getDay()));
            }
            else
                statement.setObject(parameters[paramIndex].getName(), parameters[paramIndex].getValue());
        ResultSet result = null;
        if(statement.execute())
            result = statement.getResultSet();
        return result;
    }

    private void closeConnection(Connection conn) throws SQLException
    {
        if(conn != null && !conn.isClosed())
            conn.close();
        conn = null;
    }

    private Connection getConnection() throws SQLException
    {
        if (_conn == null)
            openConnection();
        return _conn;
    }

    private void openConnection() throws SQLException
    {
        _conn = DriverManager.getConnection(Constants.SQL_CONNECTION_STRING);
    }
    // </editor-fold>
}
